﻿Public Class clsGtProcedureOperatedDetails

    Public Function getGtProcedureOperatedDetailsById(sys_id As String) As DataTable
        Dim szSql As String = ""
        szSql &= " select * from sf.b_gt_procedure_operated_details "
        szSql &= "where sys_id = ? "
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, sys_id)
        If IsNothing(dt) Then
            getGtProcedureOperatedDetailsById = New DataTable
        Else
            getGtProcedureOperatedDetailsById = dt
        End If
    End Function

    Public Function getGtProcedureOperatedDetailsByGroupNo(b_gt_sys_id As String, group_no As String) As DataTable
        Dim szSql As String = ""
        szSql &= " select * from sf.b_gt_procedure_operated_details "
        szSql &= "where b_gt_sys_id = ? and group_no = ?"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, b_gt_sys_id, group_no)
        If IsNothing(dt) Then
            getGtProcedureOperatedDetailsByGroupNo = New DataTable
        Else
            getGtProcedureOperatedDetailsByGroupNo = dt
        End If

    End Function

    Public Function getGtProcedureOperatedDetailsList(b_gt_sys_id As String) As DataTable
        Dim szSql As String = ""
        szSql &= " select * from sf.b_gt_procedure_operated_details "
        szSql &= "where b_gt_sys_id = ? order by procedure_no"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, b_gt_sys_id)
        If IsNothing(dt) Then
            getGtProcedureOperatedDetailsList = New DataTable
        Else
            getGtProcedureOperatedDetailsList = dt
        End If
    End Function

    Public Function getGtProcedureOperatedDetials(oper_type As String, b_status As String, procedure_no As String, a_status As Integer) As DataTable
        Dim szSql As String = ""
        szSql &= "select a.*,b.oper_type,b.type_value,b.batch_no from sf.b_gt_procedure_operated_details a left join sf.b_gt b on a.b_gt_sys_id = b.sys_id"
        szSql &= " where oper_type= ? and b.status in (" & b_status & ") and procedure_no = ? and a.status = ? order by update_time limit 1 "
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, oper_type, procedure_no, a_status)
        If IsNothing(dt) Then
            getGtProcedureOperatedDetials = New DataTable
        Else
            getGtProcedureOperatedDetials = dt
        End If
    End Function

    Public Function getGtProcedureOperatedDetialsLast(oper_type As String, b_status As String, procedure_no As String, a_status As Integer) As DataTable
        Dim szSql As String = ""
        szSql &= "select a.*,b.oper_type,b.type_value,b.batch_no from sf.b_gt_procedure_operated_details a left join sf.b_gt b on a.b_gt_sys_id = b.sys_id"
        szSql &= " where oper_type= ? and b.status in (" & b_status & ") and procedure_no = ? and a.status = ? order by update_time "
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, oper_type, procedure_no, a_status)
        If IsNothing(dt) Then
            getGtProcedureOperatedDetialsLast = New DataTable
        Else
            getGtProcedureOperatedDetialsLast = dt
        End If
    End Function

    Public Function save(en As entity_gt_procedure_operated_details) As Integer
        Dim sql As String = ""
        Dim rtn As Integer = 0
        DBacs.BeginTransaction()
        Try

            sql &= "UPDATE sf.b_gt_procedure "
            sql &= " SET oper_num = oper_num + ?,operable_num = operable_num - ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
            sql &= " WHERE sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(sql, -1, en.oper_num, en.oper_num, en.b_gt_procedure_sys_id)
            sql = ""
            sql &= " select * from sf.b_gt_procedure_operated where b_gt_sys_id = ? and b_gt_procedure_sys_id = ? and procedure_no = ?"
            Dim dt3 As DataTable = DBacs.ExecuteSql(sql, -1, en.b_gt_sys_id, en.b_gt_procedure_sys_id, en.procedure_No)
            sql = ""
            If dt3.Rows.Count = 1 Then
                sql &= "UPDATE sf.b_gt_procedure_operated "
                sql &= " SET oper_all_num = oper_all_num + ?,ok_all_num = ok_all_num + ?,ng_all_num = (oper_all_num + ?)-(ok_all_num + ?), update_time = (SELECT CURRENT_TIMESTAMP(6)) "
                sql &= " WHERE b_gt_sys_id = ? and b_gt_procedure_sys_id = ? and procedure_no = ? "
                rtn = DBacs.ExecuteUpdateSql(sql, -1, en.oper_num, en.oper_num, en.oper_num, en.oper_num, en.b_gt_sys_id, en.b_gt_procedure_sys_id, en.procedure_No)
            Else
                sql &= "INSERT INTO sf.b_gt_procedure_operated(procedure_no,oper_all_num,ok_all_num,b_gt_sys_id,b_gt_procedure_sys_id, update_time) "
                sql &= "VALUES (?,?,?,?,?,(SELECT CURRENT_TIMESTAMP(6)))"
                rtn = DBacs.ExecuteUpdateSql(sql, -1, en.procedure_No, en.oper_num, en.oper_num, en.b_gt_sys_id, en.b_gt_procedure_sys_id)
            End If

            sql = ""
            sql &= " select * from sf.b_gt_procedure_operated where b_gt_sys_id = ? and b_gt_procedure_sys_id = ? and procedure_no = ? order by update_time desc limit 1"
            Dim dt33 As DataTable = DBacs.ExecuteSql(sql, -1, en.b_gt_sys_id, en.b_gt_procedure_sys_id, en.procedure_No)

            sql = ""
            sql &= "INSERT INTO sf.b_gt_procedure_operated_details(procedure_no,oper_num,ok_num,status,group_no, b_gt_sys_id,b_gt_procedure_sys_id,b_gt_procedure_operated_sys_id,operator,update_time) "
            sql &= "VALUES ( ?,?,?,?,?,?,?,?,?,(SELECT CURRENT_TIMESTAMP(6)))"
            rtn = DBacs.ExecuteUpdateSql(sql, -1, en.procedure_No, en.oper_num, en.ok_num, 0, en.group_no, en.b_gt_sys_id, en.b_gt_procedure_sys_id, dt33.Rows(0)("sys_id"), en.oper)

            sql = ""
            sql &= "UPDATE sf.b_gt "
            sql &= " SET status = ?, update_time= (SELECT CURRENT_TIMESTAMP(6))"
            sql &= " WHERE sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(sql, -1, "1", en.b_gt_sys_id)

            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function save2(ty As String, en As entity_gt_procedure_operated_details) As Integer
        Dim sql As String = ""
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            '插入或更新2
            sql &= " select * from sf.b_gt_procedure_operated where b_gt_sys_id = ? and b_gt_procedure_sys_id = ? and procedure_no = ?"
            Dim dt3 As DataTable = DBacs.ExecuteSql(sql, -1, en.b_gt_sys_id, en.b_gt_procedure_sys_id, en.procedure_No)
            sql = ""
            If dt3.Rows.Count = 1 Then
                sql &= "UPDATE sf.b_gt_procedure_operated "
                sql &= " SET oper_all_num = oper_all_num + ?, ok_all_num = ok_all_num + ?, ng_all_num = (oper_all_num + ?)-(ok_all_num + ?), update_time = (SELECT CURRENT_TIMESTAMP(6)) "
                sql &= " WHERE b_gt_sys_id = ? and b_gt_procedure_sys_id = ? and procedure_no = ? "
                rtn = DBacs.ExecuteUpdateSql(sql, -1, en.ok_num, en.ok_num, en.ok_num, en.ok_num, en.b_gt_sys_id, en.b_gt_procedure_sys_id, en.procedure_No)
            Else
                sql &= "INSERT INTO sf.b_gt_procedure_operated(procedure_no,oper_all_num,ok_all_num,b_gt_sys_id,b_gt_procedure_sys_id, update_time) "
                sql &= "VALUES (?,?,?,?,?,(SELECT CURRENT_TIMESTAMP(6)))"
                rtn = DBacs.ExecuteUpdateSql(sql, -1, en.procedure_No, en.ok_num, en.ok_num, en.b_gt_sys_id, en.b_gt_procedure_sys_id)
            End If

            Dim bl_b_gt_procedure As Boolean = False
            If ty.Equals("0") Then
                If en.procedure_No.Equals("3") Then
                    bl_b_gt_procedure = True
                End If
            End If
            If ty.Equals("1") Then
                If en.procedure_No.Equals("2") Then
                    bl_b_gt_procedure = True
                End If
            End If
            If bl_b_gt_procedure Then
                sql = ""
                sql &= "UPDATE sf.b_gt_procedure "
                sql &= " SET ok_num = ok_num + ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
                sql &= " WHERE sys_id = ?"
                rtn = DBacs.ExecuteUpdateSql(sql, -1, en.ok_num, en.b_gt_procedure_sys_id)

                '更新b_gt
                sql = ""
                sql &= "UPDATE sf.b_gt"
                sql &= " SET ok_num = ok_num + ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
                sql &= " WHERE sys_id = ? "
                rtn = DBacs.ExecuteUpdateSql(sql, -1, en.ok_num, en.b_gt_sys_id)

                sql = ""
                sql &= " select * from sf.b_gt_procedure where b_gt_sys_id = ? "
                Dim dt00 As DataTable = DBacs.ExecuteSql(sql, -1, en.b_gt_sys_id)
                Dim tem_operable_num As Integer = Integer.Parse(dt00.Rows(0)("operable_num").ToString())
                If tem_operable_num = 0 Then
                    sql = ""
                    sql &= "UPDATE sf.b_gt"
                    sql &= " SET status = ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
                    sql &= " WHERE sys_id = ? "
                    rtn = DBacs.ExecuteUpdateSql(sql, -1, "2", en.b_gt_sys_id)
                End If
            End If

            '更新1
            Dim temp_ng_num As Integer = en.oper_num - en.ok_num
            sql = ""
            sql &= "UPDATE sf.b_gt_procedure_operated "
            sql &= " SET ok_all_num = oper_all_num - (ng_all_num + ?),ng_all_num = ng_all_num + ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
            sql &= " WHERE sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(sql, -1, temp_ng_num, temp_ng_num, en.b_gt_procedure_operated_sys_id)

            sql = ""
            sql &= " select * from sf.b_gt_procedure_operated where b_gt_sys_id = ? and b_gt_procedure_sys_id = ? and procedure_no = ? order by update_time desc limit 1"
            Dim dt33 As DataTable = DBacs.ExecuteSql(sql, -1, en.b_gt_sys_id, en.b_gt_procedure_sys_id, en.procedure_No)

            sql = ""
            sql &= "INSERT INTO sf.b_gt_procedure_operated_details(procedure_no,oper_num,ok_num,status,group_no, b_gt_sys_id,b_gt_procedure_sys_id,b_gt_procedure_operated_sys_id,operator,update_time) "
            sql &= "VALUES ( ?,?,?,?,?,?,?,?,?,(SELECT CURRENT_TIMESTAMP(6)))"
            rtn = DBacs.ExecuteUpdateSql(sql, -1, en.procedure_No, en.ok_num, en.ok_num, 0, en.group_no, en.b_gt_sys_id, en.b_gt_procedure_sys_id, dt33.Rows(0)("sys_id"), en.oper)

            '更新1
            sql = ""
            sql &= "UPDATE sf.b_gt_procedure_operated_details "
            sql &= " SET ng_num = oper_num - ?, ok_num = ?,status = ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
            sql &= " WHERE sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(sql, -1, en.ok_num, en.ok_num, 1, en.sys_id)

            If en.oper_num - en.ok_num > 0 Then
                Dim pn As String = (Integer.Parse(en.procedure_No) - 1).ToString()
                sql = ""
                sql &= "INSERT INTO sf.b_gt_procedure_operated_ng_details(procedure_No, ng_num, operator, validity,b_gt_procedure_operated_details_sys_id, b_gt_sys_id, update_time) "
                sql &= "VALUES ( ?, ?, ?, ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6)))"
                rtn = DBacs.ExecuteUpdateSql(sql, -1, pn, temp_ng_num, en.oper, 1, en.sys_id, en.b_gt_sys_id)

                sql = ""
                sql &= "UPDATE sf.b_gt_procedure "
                sql &= " SET ng_num = ng_num + ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
                sql &= " WHERE sys_id = ?"
                rtn = DBacs.ExecuteUpdateSql(sql, -1, temp_ng_num, en.b_gt_procedure_sys_id)

                '更新b_gt
                sql = ""
                sql &= "UPDATE sf.b_gt"
                sql &= " SET ng_num = ng_num + ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
                sql &= " WHERE sys_id = ? "
                rtn = DBacs.ExecuteUpdateSql(sql, -1, temp_ng_num, en.b_gt_sys_id)

                sql = ""
                sql &= " select * from sf.b_gt_ng where b_gt_sys_id = ?"
                Dim dtxx As DataTable = DBacs.ExecuteSql(sql, -1, en.b_gt_sys_id)
                sql = ""
                If dtxx.Rows.Count = 1 Then
                    sql &= "UPDATE sf.b_gt_ng "
                    sql &= " SET oper_ng_num = oper_ng_num + ?, all_ng_num = un_oper_ng_num + oper_ng_num + ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
                    sql &= " WHERE b_gt_sys_id = ? "
                    rtn = DBacs.ExecuteUpdateSql(sql, -1, temp_ng_num, temp_ng_num, en.b_gt_sys_id)
                Else
                    sql &= "INSERT INTO sf.b_gt_ng(all_ng_num,oper_ng_num,b_gt_sys_id,update_time) "
                    sql &= "VALUES (?,?,?,(SELECT CURRENT_TIMESTAMP(6)))"
                    rtn = DBacs.ExecuteUpdateSql(sql, -1, temp_ng_num, temp_ng_num, en.b_gt_sys_id)
                End If

            End If

            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function updateGtProcedureOperatedDetails(sys_id As String,
                                                    procedure_no As Integer,
                                                    oper_num As String) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            szSql &= "update sf.b_gt_procedure_operated_details set oper_num = ?,ok_num = ? - ng_num, where sys_id = ?"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, oper_num, oper_num, sys_id)
            If procedure_no > 0 Then

                szSql = ""
                szSql &= " select * from sf.b_gt_procedure_operated_details where sys_id = ? "
                Dim dt3 As DataTable = DBacs.ExecuteSql(szSql, -1, sys_id)

                szSql = ""
                szSql &= " select * from sf.b_gt_procedure_operated_details where b_gt_procedure_operated_sys_id = ? and procedure_no = ?"
                Dim dt33 As DataTable = DBacs.ExecuteSql(szSql, -1, dt3.Rows(0)("b_gt_procedure_operated_sys_id"), procedure_no.ToString())

                Dim dt33_oper_num As Object = dt33.Compute("sum(oper_num)", "")
                Dim dt33_ng_num As Object = dt33.Compute("sum(ng_num)", "")
                Dim dt33_ok_num As Object = dt33.Compute("sum(ok_num)", "")

                szSql = String.Empty
                szSql &= "UPDATE sf.b_gt_procedure_operated "
                szSql &= " SET ng_all_num = ?, ok_all_num = ?,oper_all_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE sys_id = ? "
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, dt33_ng_num, dt33_ok_num, dt33_oper_num, dt3.Rows(0)("b_gt_procedure_operated_sys_id"))

                szSql = ""
                szSql &= " select * from sf.b_gt_procedure_operated where sys_id = ? "
                Dim dt2 As DataTable = DBacs.ExecuteSql(szSql, -1, dt3.Rows(0)("b_gt_procedure_operated_sys_id"))

                szSql = ""
                szSql &= " select * from sf.b_gt where sys_id = ? "
                Dim dt22 As DataTable = DBacs.ExecuteSql(szSql, -1, dt2.Rows(0)("b_gt_sys_id"))
                If procedure_no = 1 Then
                    Dim picking_num As Integer = Integer.Parse(dt22.Rows(0)("picking_num").ToString())
                    Dim operable_num As Integer = picking_num - dt33_oper_num
                    szSql = String.Empty
                    szSql &= "UPDATE sf.b_gt_procedure "
                    szSql &= " SET oper_num = ?, operable_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                    szSql &= " WHERE sys_id = ? "
                    DBacs.ExecuteUpdateSql(szSql, -1, dt33_oper_num, operable_num, dt3.Rows(0)("b_gt_procedure_sys_id"))

                End If
                If (dt22.Rows(0)("oper_type").ToString().Equals("0") And procedure_no = 3) Or
                    (dt22.Rows(0)("oper_type").ToString().Equals("1") And procedure_no = 2) Then
                    szSql = String.Empty
                    szSql &= "UPDATE sf.b_gt_procedure "
                    szSql &= " SET ng_num = ?,ok_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                    szSql &= " WHERE sys_id = ? "
                    DBacs.ExecuteUpdateSql(szSql, -1, dt33_ng_num, dt33_ok_num, dt3.Rows(0)("b_gt_procedure_sys_id"))
                End If
            Else

            End If

            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
        End Try
        Return rtn
    End Function

    Public Function updateGtProcedureOperatedDetailsGjb(oper_num1 As Integer, ng_num1 As Integer, sys_id1 As String,
                                                        oper_num2 As Integer, ng_num2 As Integer, sys_id2 As String,
                                                        oper_num3 As Integer, ng_num3 As Integer, sys_id3 As String) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            szSql &= "update sf.b_gt_procedure_operated_details set oper_num = ?,ok_num = ?, ng_num = ? where sys_id = ?"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, oper_num1, oper_num1 - ng_num1, ng_num1, sys_id1)

            szSql = String.Empty
            szSql &= " select * from sf.b_gt_procedure_operated_ng_details where b_gt_procedure_operated_details_sys_id = ? "
            Dim dtng1 As DataTable = DBacs.ExecuteSql(szSql, -1, sys_id1)
            If dtng1.Rows.Count = 1 Then
                If ng_num1 > 0 Then
                    szSql = String.Empty
                    szSql &= "UPDATE sf.b_gt_procedure_operated_ng_details "
                    szSql &= " SET ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                    szSql &= " WHERE sys_id = ? "
                    rtn = DBacs.ExecuteUpdateSql(szSql, -1, ng_num1, dtng1.Rows(0)("sys_id"))
                Else
                    szSql = String.Empty
                    szSql &= "UPDATE sf.b_gt_procedure_operated_ng_details "
                    szSql &= " SET ng_num = ?,validity = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                    szSql &= " WHERE sys_id = ? "
                    rtn = DBacs.ExecuteUpdateSql(szSql, -1, ng_num1, 0, dtng1.Rows(0)("sys_id"))
                End If
            End If

            szSql = String.Empty
            szSql &= " select * from sf.b_gt_procedure_operated_details where sys_id = ? "
            Dim dt1 As DataTable = DBacs.ExecuteSql(szSql, -1, sys_id1)

            szSql = String.Empty
            szSql &= " select * from sf.b_gt_procedure_operated_details where b_gt_procedure_operated_sys_id = ? and procedure_no = ?"
            Dim dt11 As DataTable = DBacs.ExecuteSql(szSql, -1, dt1.Rows(0)("b_gt_procedure_operated_sys_id"), "1")

            Dim dt11_oper_num As Object = dt11.Compute("sum(oper_num)", "")
            Dim dt11_ng_num As Object = dt11.Compute("sum(ng_num)", "")
            Dim dt11_ok_num As Object = dt11.Compute("sum(ok_num)", "")

            szSql = String.Empty
            szSql &= "UPDATE sf.b_gt_procedure_operated "
            szSql &= " SET ng_all_num = ?, ok_all_num = ?,oper_all_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
            szSql &= " WHERE sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, dt11_ng_num, dt11_ok_num, dt11_oper_num, dt1.Rows(0)("b_gt_procedure_operated_sys_id"))

            szSql = String.Empty
            szSql &= " select * from sf.b_gt_procedure_operated_ng_details where b_gt_procedure_operated_details_sys_id = ? "
            Dim dtng2 As DataTable = DBacs.ExecuteSql(szSql, -1, sys_id2)
            If dtng2.Rows.Count = 1 Then
                If ng_num2 > 0 Then
                    szSql = String.Empty
                    szSql &= "UPDATE sf.b_gt_procedure_operated_ng_details "
                    szSql &= " SET ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                    szSql &= " WHERE sys_id = ? "
                    rtn = DBacs.ExecuteUpdateSql(szSql, -1, ng_num2, dtng2.Rows(0)("sys_id"))
                Else
                    szSql = String.Empty
                    szSql &= "UPDATE sf.b_gt_procedure_operated_ng_details "
                    szSql &= " SET ng_num = ?,validity = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                    szSql &= " WHERE sys_id = ? "
                    rtn = DBacs.ExecuteUpdateSql(szSql, -1, ng_num2, 0, dtng2.Rows(0)("sys_id"))
                End If
            End If


            szSql = String.Empty
            szSql &= "update sf.b_gt_procedure_operated_details set oper_num = ?,ok_num = ?, ng_num = ? where sys_id = ?"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, oper_num2, oper_num2 - ng_num2, ng_num2, sys_id2)

            szSql = String.Empty
            szSql &= " select * from sf.b_gt_procedure_operated_ng_details where b_gt_procedure_operated_details_sys_id = ? "
            Dim dtng3 As DataTable = DBacs.ExecuteSql(szSql, -1, sys_id3)
            If dtng3.Rows.Count = 1 Then
                If ng_num3 > 0 Then
                    szSql = String.Empty
                    szSql &= "UPDATE sf.b_gt_procedure_operated_ng_details "
                    szSql &= " SET ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                    szSql &= " WHERE sys_id = ? "
                    rtn = DBacs.ExecuteUpdateSql(szSql, -1, ng_num3, dtng3.Rows(0)("sys_id"))
                Else
                    szSql = String.Empty
                    szSql &= "UPDATE sf.b_gt_procedure_operated_ng_details "
                    szSql &= " SET ng_num = ?,validity = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                    szSql &= " WHERE sys_id = ? "
                    rtn = DBacs.ExecuteUpdateSql(szSql, -1, ng_num3, 0, dtng3.Rows(0)("sys_id"))
                End If
            End If


            szSql = String.Empty
            szSql &= " select * from sf.b_gt_procedure_operated_details where sys_id = ? "
            Dim dt2 As DataTable = DBacs.ExecuteSql(szSql, -1, sys_id2)

            szSql = String.Empty
            szSql &= " select * from sf.b_gt_procedure_operated_details where b_gt_procedure_operated_sys_id = ? and procedure_no = ?"
            Dim dt22 As DataTable = DBacs.ExecuteSql(szSql, -1, dt2.Rows(0)("b_gt_procedure_operated_sys_id"), "2")

            Dim dt22_oper_num As Object = dt22.Compute("sum(oper_num)", "")
            Dim dt22_ng_num As Object = dt22.Compute("sum(ng_num)", "")
            Dim dt22_ok_num As Object = dt22.Compute("sum(ok_num)", "")

            szSql = String.Empty
            szSql &= "UPDATE sf.b_gt_procedure_operated "
            szSql &= " SET ng_all_num = ?, ok_all_num = ?,oper_all_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
            szSql &= " WHERE sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, dt22_ng_num, dt22_ok_num, dt22_oper_num, dt2.Rows(0)("b_gt_procedure_operated_sys_id"))


            szSql = String.Empty
            szSql &= "update sf.b_gt_procedure_operated_details set oper_num = ?,ok_num = ?, ng_num = ? where sys_id = ?"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, oper_num3, oper_num3 - ng_num3, ng_num3, sys_id3)

            szSql = String.Empty
            szSql &= " select * from sf.b_gt_procedure_operated_details where sys_id = ? "
            Dim dt3 As DataTable = DBacs.ExecuteSql(szSql, -1, sys_id3)

            szSql = String.Empty
            szSql &= " select * from sf.b_gt_procedure_operated_details where b_gt_procedure_operated_sys_id = ? and procedure_no = ?"
            Dim dt33 As DataTable = DBacs.ExecuteSql(szSql, -1, dt3.Rows(0)("b_gt_procedure_operated_sys_id"), "3")

            Dim dt33_oper_num As Object = dt33.Compute("sum(oper_num)", "")
            Dim dt33_ng_num As Object = dt33.Compute("sum(ng_num)", "")
            Dim dt33_ok_num As Object = dt33.Compute("sum(ok_num)", "")

            szSql = String.Empty
            szSql &= "UPDATE sf.b_gt_procedure_operated "
            szSql &= " SET ng_all_num = ?, ok_all_num = ?,oper_all_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
            szSql &= " WHERE sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, dt33_ng_num, dt33_ok_num, dt33_oper_num, dt3.Rows(0)("b_gt_procedure_operated_sys_id"))

            szSql = ""
            szSql &= " select * from sf.b_gt_procedure where sys_id = ? "
            Dim dtgtp As DataTable = DBacs.ExecuteSql(szSql, -1, dt3.Rows(0)("b_gt_procedure_sys_id"))
            Dim operable_num As Integer = Integer.Parse(dtgtp.Rows(0)("operable_num").ToString())


            szSql = ""
            szSql &= " select * from sf.b_gt where sys_id = ? "
            Dim dtgt As DataTable = DBacs.ExecuteSql(szSql, -1, dt3.Rows(0)("b_gt_sys_id"))

            Dim picking_num As Integer = Integer.Parse(dtgt.Rows(0)("picking_num").ToString())
            Dim rtn_operable_num As Integer = picking_num - dt11_oper_num

            If operable_num = 0 Then
                szSql = String.Empty
                szSql &= "UPDATE sf.b_gt_procedure "
                szSql &= " SET oper_num = ?, ok_num = ?, ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE sys_id = ? "
                DBacs.ExecuteUpdateSql(szSql, -1, dt11_oper_num, dt33_ok_num, dt11_oper_num - dt33_ok_num, dt3.Rows(0)("b_gt_procedure_sys_id"))

                '更新ng 记录
                szSql = String.Empty
                szSql &= "UPDATE sf.b_gt_procedure_operated_ng_details "
                szSql &= " SET ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE b_gt_procedure_operated_details_sys_id = ? and procedure_no = ?"
                DBacs.ExecuteUpdateSql(szSql, -1, rtn_operable_num, dt3.Rows(0)("sys_id"), "0")

                szSql = String.Empty
                szSql &= "UPDATE sf.b_gt_ng "
                szSql &= " SET all_ng_num = ?,oper_ng_num = ?, un_oper_ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE b_gt_sys_id = ? "
                DBacs.ExecuteUpdateSql(szSql, -1, dt11_ng_num + dt22_ng_num + dt33_ng_num, dt11_ng_num + dt22_ng_num + dt33_ng_num, picking_num - (dt11_ng_num + dt22_ng_num + dt33_ng_num), dt3.Rows(0)("sys_id"))

                szSql = String.Empty
                szSql &= "UPDATE sf.b_gt "
                szSql &= " SET ok_num = ?,ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE sys_id = ? "
                DBacs.ExecuteUpdateSql(szSql, -1, dt33_ok_num, dt11_ng_num + dt22_ng_num + dt33_ng_num + rtn_operable_num, dt3.Rows(0)("b_gt_sys_id"))


            Else
                szSql = String.Empty
                szSql &= "UPDATE sf.b_gt_procedure "
                szSql &= " SET operable_num = ?, oper_num = ?, ok_num = ?, ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE sys_id = ? "
                DBacs.ExecuteUpdateSql(szSql, -1, rtn_operable_num, dt11_oper_num, dt33_ok_num, dt11_oper_num - dt33_ok_num, dt3.Rows(0)("b_gt_procedure_sys_id"))

                szSql = String.Empty
                szSql &= "UPDATE sf.b_gt_ng "
                szSql &= " SET all_ng_num = ?,oper_ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE b_gt_sys_id = ? "
                DBacs.ExecuteUpdateSql(szSql, -1, dt11_ng_num + dt22_ng_num + dt33_ng_num, dt11_ng_num + dt22_ng_num + dt33_ng_num, dt3.Rows(0)("b_gt_sys_id"))

                szSql = String.Empty
                szSql &= "UPDATE sf.b_gt "
                szSql &= " SET ok_num = ?,ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE sys_id = ? "
                DBacs.ExecuteUpdateSql(szSql, -1, dt33_ok_num, dt11_ng_num + dt22_ng_num + dt33_ng_num, dt3.Rows(0)("b_gt_sys_id"))

            End If

            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
        End Try
        Return rtn
    End Function

    Public Function updateGtProcedureOperatedDetailsFlb(oper_num1 As Integer, ng_num1 As Integer, sys_id1 As String,
                                                        oper_num2 As Integer, ng_num2 As Integer, sys_id2 As String) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            szSql &= "update sf.b_gt_procedure_operated_details set oper_num = ?,ok_num = ?, ng_num = ? where sys_id = ?"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, oper_num1, oper_num1 - ng_num1, ng_num1, sys_id1)

            szSql = String.Empty
            szSql &= " select * from sf.b_gt_procedure_operated_ng_details where b_gt_procedure_operated_details_sys_id = ? "
            Dim dtng1 As DataTable = DBacs.ExecuteSql(szSql, -1, sys_id1)
            If dtng1.Rows.Count = 1 Then
                If ng_num1 > 0 Then
                    szSql = String.Empty
                    szSql &= "UPDATE sf.b_gt_procedure_operated_ng_details "
                    szSql &= " SET ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                    szSql &= " WHERE sys_id = ? "
                    rtn = DBacs.ExecuteUpdateSql(szSql, -1, ng_num1, dtng1.Rows(0)("sys_id"))
                Else
                    szSql = String.Empty
                    szSql &= "UPDATE sf.b_gt_procedure_operated_ng_details "
                    szSql &= " SET ng_num = ?,validity = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                    szSql &= " WHERE sys_id = ? "
                    rtn = DBacs.ExecuteUpdateSql(szSql, -1, ng_num1, 0, dtng1.Rows(0)("sys_id"))
                End If
            End If

            szSql = String.Empty
            szSql &= " select * from sf.b_gt_procedure_operated_details where sys_id = ? "
            Dim dt1 As DataTable = DBacs.ExecuteSql(szSql, -1, sys_id1)

            szSql = String.Empty
            szSql &= " select * from sf.b_gt_procedure_operated_details where b_gt_procedure_operated_sys_id = ? and procedure_no = ?"
            Dim dt11 As DataTable = DBacs.ExecuteSql(szSql, -1, dt1.Rows(0)("b_gt_procedure_operated_sys_id"), "1")

            Dim dt11_oper_num As Object = dt11.Compute("sum(oper_num)", "")
            Dim dt11_ng_num As Object = dt11.Compute("sum(ng_num)", "")
            Dim dt11_ok_num As Object = dt11.Compute("sum(ok_num)", "")

            szSql = String.Empty
            szSql &= "UPDATE sf.b_gt_procedure_operated "
            szSql &= " SET ng_all_num = ?, ok_all_num = ?,oper_all_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
            szSql &= " WHERE sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, dt11_ng_num, dt11_ok_num, dt11_oper_num, dt1.Rows(0)("b_gt_procedure_operated_sys_id"))


            szSql = String.Empty
            szSql &= "update sf.b_gt_procedure_operated_details set oper_num = ?,ok_num = ?, ng_num = ? where sys_id = ?"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, oper_num2, oper_num2 - ng_num2, ng_num2, sys_id2)

            szSql = String.Empty
            szSql &= " select * from sf.b_gt_procedure_operated_ng_details where b_gt_procedure_operated_details_sys_id = ? "
            Dim dtng2 As DataTable = DBacs.ExecuteSql(szSql, -1, sys_id2)
            If dtng2.Rows.Count = 1 Then
                If ng_num2 > 0 Then
                    szSql = String.Empty
                    szSql &= "UPDATE sf.b_gt_procedure_operated_ng_details "
                    szSql &= " SET ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                    szSql &= " WHERE sys_id = ? "
                    rtn = DBacs.ExecuteUpdateSql(szSql, -1, ng_num2, dtng2.Rows(0)("sys_id"))
                Else
                    szSql = String.Empty
                    szSql &= "UPDATE sf.b_gt_procedure_operated_ng_details "
                    szSql &= " SET ng_num = ?,validity = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                    szSql &= " WHERE sys_id = ? "
                    rtn = DBacs.ExecuteUpdateSql(szSql, -1, ng_num2, 0, dtng2.Rows(0)("sys_id"))
                End If
            End If


            szSql = String.Empty
            szSql &= " select * from sf.b_gt_procedure_operated_details where sys_id = ? "
            Dim dt2 As DataTable = DBacs.ExecuteSql(szSql, -1, sys_id2)

            szSql = String.Empty
            szSql &= " select * from sf.b_gt_procedure_operated_details where b_gt_procedure_operated_sys_id = ? and procedure_no = ?"
            Dim dt22 As DataTable = DBacs.ExecuteSql(szSql, -1, dt2.Rows(0)("b_gt_procedure_operated_sys_id"), "2")

            Dim dt22_oper_num As Object = dt22.Compute("sum(oper_num)", "")
            Dim dt22_ng_num As Object = dt22.Compute("sum(ng_num)", "")
            Dim dt22_ok_num As Object = dt22.Compute("sum(ok_num)", "")

            szSql = String.Empty
            szSql &= "UPDATE sf.b_gt_procedure_operated "
            szSql &= " SET ng_all_num = ?, ok_all_num = ?,oper_all_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
            szSql &= " WHERE sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, dt22_ng_num, dt22_ok_num, dt22_oper_num, dt2.Rows(0)("b_gt_procedure_operated_sys_id"))

            szSql = ""
            szSql &= " select * from sf.b_gt_procedure where sys_id = ? "
            Dim dtgtp As DataTable = DBacs.ExecuteSql(szSql, -1, dt2.Rows(0)("b_gt_procedure_sys_id"))
            Dim operable_num As Integer = Integer.Parse(dtgtp.Rows(0)("operable_num").ToString())


            szSql = ""
            szSql &= " select * from sf.b_gt where sys_id = ? "
            Dim dtgt As DataTable = DBacs.ExecuteSql(szSql, -1, dt2.Rows(0)("b_gt_sys_id"))

            Dim picking_num As Integer = Integer.Parse(dtgt.Rows(0)("picking_num").ToString())
            Dim rtn_operable_num As Integer = picking_num - dt11_oper_num

            If operable_num = 0 Then
                szSql = String.Empty
                szSql &= "UPDATE sf.b_gt_procedure "
                szSql &= " SET oper_num = ?, ok_num = ?, ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE sys_id = ? "
                DBacs.ExecuteUpdateSql(szSql, -1, dt11_oper_num, dt22_ok_num, dt11_ng_num + dt22_ng_num, dt2.Rows(0)("b_gt_procedure_sys_id"))

                '更新ng 记录

                szSql = String.Empty
                szSql &= "UPDATE sf.b_gt_procedure_operated_ng_details "
                szSql &= " SET ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE b_gt_procedure_operated_details_sys_id = ? and procedure_no = ?"
                DBacs.ExecuteUpdateSql(szSql, -1, rtn_operable_num, dt2.Rows(0)("sys_id"), "0")


                szSql = String.Empty
                szSql &= "UPDATE sf.b_gt_ng "
                szSql &= " SET all_ng_num = ?,oper_ng_num = ?, un_oper_ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE b_gt_sys_id = ? "
                DBacs.ExecuteUpdateSql(szSql, -1, dt11_ng_num + dt22_ng_num, dt11_ng_num + dt22_ng_num, picking_num - (dt11_ng_num + dt22_ng_num), dt2.Rows(0)("sys_id"))

                szSql = String.Empty
                szSql &= "UPDATE sf.b_gt "
                szSql &= " SET ok_num = ?,ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE sys_id = ? "
                DBacs.ExecuteUpdateSql(szSql, -1, dt22_ok_num, dt11_ng_num + dt22_ng_num + rtn_operable_num, dt2.Rows(0)("b_gt_sys_id"))


            Else
                szSql = String.Empty
                szSql &= "UPDATE sf.b_gt_procedure "
                szSql &= " SET operable_num = ?, oper_num = ?, ok_num = ?, ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE sys_id = ? "
                DBacs.ExecuteUpdateSql(szSql, -1, rtn_operable_num, dt11_oper_num, dt22_ok_num, dt11_ng_num + dt22_ng_num, dt2.Rows(0)("b_gt_procedure_sys_id"))

                szSql = String.Empty
                szSql &= "UPDATE sf.b_gt_ng "
                szSql &= " SET all_ng_num = ?,oper_ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE b_gt_sys_id = ? "
                DBacs.ExecuteUpdateSql(szSql, -1, dt11_ng_num + dt22_ng_num, dt11_ng_num + dt22_ng_num, dt2.Rows(0)("b_gt_sys_id"))

                szSql = String.Empty
                szSql &= "UPDATE sf.b_gt "
                szSql &= " SET ok_num = ?,ng_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE sys_id = ? "
                DBacs.ExecuteUpdateSql(szSql, -1, dt22_ok_num, dt11_ng_num + dt22_ng_num, dt2.Rows(0)("b_gt_sys_id"))

            End If

            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
        End Try
        Return rtn
    End Function

End Class
